/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @authors: Pat, Jamie, Kazu, Stephen, and Shaoyan
 */
import java.sql.*;

/**
 * 
 * @authors: Pat, Jamie, Kazu, Stephen, and Shaoyan
 */
public class Billing {
    private int customerID;
    private int overduePeriod;
    private int billingDate;
    private int dueDate;
    private DB db;

    /**
     * 
     */
    public Billing(){
        this.db = new DB();
    }

    /**
     * 
     * @param customerID
     * @param billingDate
     * @param dueDate
     */
    public Billing(int customerID, int billingDate, int dueDate) {
            this.customerID = customerID;
            this.overduePeriod = 0;
            this.dueDate = dueDate;
            this.billingDate = billingDate;
            this.db = new DB();
    }

    /**
     * 
     * @param custID
     * @return
     */
    public double getBalance(int custID){
        Connection conn = db.connect();
        String query = "SELECT * FROM BillingDB WHERE customerId = '" + custID 
                + "' AND paid = 0";
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        double balance = 0.;
        try{
            while(result.next()){
                balance += result.getDouble("billAmount");
            }
        }catch(Exception e){ }
        return balance;
    }

    /**
     * 
     * @param custID
     * @param billingStartDate
     * @param billingEndDate
     * @param billingDueDate
     * @param amount
     * @param paid
     * @param text
     */
    public void addBill(int custID, String billingStartDate, String billingEndDate, String billingDueDate,double amount, int paid,String text){
        Connection conn = db.connect();
        String query = "INSERT INTO BillingDB (customerId,billingStartDate,billingEndDate,billingDueDate,billAmount,paid,notes) "
            +"VALUES ('" + custID + "','" + billingStartDate + "','" + billingEndDate + "','" + billingDueDate + "','" + amount +"','"+paid+ "','" + text
            + "')";
        db.putQuery(conn, query);
        db.disconnect(conn);
    }

    /**
     * 
     * @return
     */
    public int getBillingDate() {
            return this.billingDate;
    }

    /**
     * 
     * @return
     */
    public int getDueDate() {
            return this.dueDate;
    }

    /**
     * 
     * @param endTime
     * @param dueDate
     * @return
     */
    public double calculateOverduePeriod(double endTime, int dueDate) {
            double _overFlow = (endTime-dueDate)/(0.25);
            return _overFlow;
    }
    /**
     * 
     * @param custID
     * @param vehicleID
     * @param date
     * @param endTime
     * @param returnedTime
     * @return
     */
    public String returnOnTime(int custID, int vehicleID,String date, double endTime, int returnedTime){
        double overflow = 0;
        overflow = (returnedTime - endTime)/(0.25);

        Customer cust = new Customer();
        int hoursLeft = cust.getHoursLeft(custID);
        if(overflow <= 0)
            return "No penalty";
        else if(overflow > 0 && overflow > hoursLeft){
            cust.updateHours(custID,(int)overflow*-1);
            return "Penalty taken off from hours left";
        }
        else{
            cust.updateHours(custID, hoursLeft*-1);
            overflow = overflow - hoursLeft;
            addBill(custID, date,date, cust.getExpiryDate(custID),overflow,0, "Late returning vehicle");
            return "Penalty add as a bill";
        }
    }

    /**
     * 
     * @param custID
     * @return
     */
    public ResultSet getBills(int custID){
        String query = "SELECT * FROM BillingDB WHERE customerId = "+custID+" ORDER BY billingId";
        Connection conn = db.connect();
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        return result;
    }
    
    public ResultSet getRangedBills(int custID, String tempStartPeriod, String tempEndPeriod){
        Date startPeriod = Date.valueOf(tempStartPeriod);
        Date endPeriod = Date.valueOf(tempEndPeriod);
        //System.out.println(startPeriod+" - "+endPeriod);
        String query = "SELECT * FROM BillingDB WHERE customerId = "+custID
                +" AND ((billingStartDate > '"+startPeriod
                +"' AND billingStartDate < '"+endPeriod
                +"') OR (billingEndDate > '"+startPeriod
                +"' AND billingEndDate < '"+endPeriod
                +"')) ORDER BY billingId";
        //System.out.println(query);
        Connection conn = db.connect();
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        return result;
    }
}